// 访客统计模型
const { postgresPool } = require('../config/database');

class Visitor {
    // 记录访客事件
    static async recordEvent(eventData) {
        const {
            visitor_id,
            session_id,
            event_type,
            page_url,
            referrer,
            user_agent,
            ip_address,
            country,
            device_type,
            browser,
            event_data
        } = eventData;
        
        const query = `
            INSERT INTO visitor_events 
            (visitor_id, session_id, event_type, page_url, referrer, user_agent, ip_address, country, device_type, browser, event_data)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
            RETURNING *
        `;
        
        const values = [
            visitor_id, session_id, event_type, page_url, referrer, user_agent, 
            ip_address, country, device_type, browser, event_data
        ];
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows[0];
        } catch (error) {
            console.error('记录访客事件失败:', error);
            throw error;
        }
    }
    
    // 更新访客统计
    static async updateStats(statsData) {
        const {
            date,
            page_views = 0,
            unique_visitors = 0,
            bounce_rate = 0,
            avg_session_duration = 0,
            referrer,
            country,
            device_type
        } = statsData;
        
        const query = `
            INSERT INTO visitor_stats 
            (date, page_views, unique_visitors, bounce_rate, avg_session_duration, referrer, country, device_type)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
            ON CONFLICT (date, country, device_type) 
            DO UPDATE SET 
                page_views = visitor_stats.page_views + EXCLUDED.page_views,
                unique_visitors = visitor_stats.unique_visitors + EXCLUDED.unique_visitors,
                bounce_rate = (visitor_stats.bounce_rate + EXCLUDED.bounce_rate) / 2,
                avg_session_duration = (visitor_stats.avg_session_duration + EXCLUDED.avg_session_duration) / 2,
                updated_at = CURRENT_TIMESTAMP
            RETURNING *
        `;
        
        const values = [date, page_views, unique_visitors, bounce_rate, avg_session_duration, referrer, country, device_type];
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows[0];
        } catch (error) {
            console.error('更新访客统计失败:', error);
            throw error;
        }
    }
    
    // 获取访客统计摘要
    static async getSummary(startDate, endDate) {
        const query = `
            SELECT 
                COUNT(*) as total_events,
                COUNT(DISTINCT visitor_id) as unique_visitors,
                COUNT(DISTINCT session_id) as total_sessions,
                AVG(EXTRACT(EPOCH FROM (MAX(created_at) - MIN(created_at)))) as avg_session_duration,
                SUM(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) as page_views,
                SUM(CASE WHEN event_type = 'conversion' THEN 1 ELSE 0 END) as conversions
            FROM visitor_events
            WHERE created_at BETWEEN $1 AND $2
        `;
        
        const values = [startDate, endDate];
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows[0];
        } catch (error) {
            console.error('获取访客统计摘要失败:', error);
            throw error;
        }
    }
    
    // 获取热门页面
    static async getTopPages(startDate, endDate, limit = 10) {
        const query = `
            SELECT 
                page_url,
                COUNT(*) as views,
                COUNT(DISTINCT visitor_id) as unique_visitors
            FROM visitor_events
            WHERE event_type = 'page_view' 
                AND created_at BETWEEN $1 AND $2
                AND page_url IS NOT NULL
            GROUP BY page_url
            ORDER BY views DESC
            LIMIT $3
        `;
        
        const values = [startDate, endDate, limit];
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows;
        } catch (error) {
            console.error('获取热门页面失败:', error);
            throw error;
        }
    }
    
    // 获取地理分布
    static async getGeoDistribution(startDate, endDate) {
        const query = `
            SELECT 
                country,
                COUNT(*) as total_events,
                COUNT(DISTINCT visitor_id) as unique_visitors
            FROM visitor_events
            WHERE created_at BETWEEN $1 AND $2
                AND country IS NOT NULL
            GROUP BY country
            ORDER BY total_events DESC
        `;
        
        const values = [startDate, endDate];
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows;
        } catch (error) {
            console.error('获取地理分布失败:', error);
            throw error;
        }
    }
    
    // 获取设备统计
    static async getDeviceStats(startDate, endDate) {
        const query = `
            SELECT 
                device_type,
                browser,
                COUNT(*) as total_events,
                COUNT(DISTINCT visitor_id) as unique_visitors
            FROM visitor_events
            WHERE created_at BETWEEN $1 AND $2
                AND device_type IS NOT NULL
            GROUP BY device_type, browser
            ORDER BY total_events DESC
        `;
        
        const values = [startDate, endDate];
        
        try {
            const result = await postgresPool.query(query, values);
            return result.rows;
        } catch (error) {
            console.error('获取设备统计失败:', error);
            throw error;
        }
    }
    
    // 获取实时访客数据
    static async getRealTimeStats(hours = 24) {
        const query = `
            SELECT 
                DATE_TRUNC('hour', created_at) as hour,
                COUNT(*) as events,
                COUNT(DISTINCT visitor_id) as unique_visitors
            FROM visitor_events
            WHERE created_at >= NOW() - INTERVAL '${hours} hours'
            GROUP BY DATE_TRUNC('hour', created_at)
            ORDER BY hour DESC
        `;
        
        try {
            const result = await postgresPool.query(query);
            return result.rows;
        } catch (error) {
            console.error('获取实时统计失败:', error);
            throw error;
        }
    }
}

module.exports = Visitor;